# -*- coding: utf-8 -*-
from scpy.logger import get_logger
import os
import sys
from util.pgutil import *


reload(sys)
sys.setdefaultencoding('utf-8')

logger = get_logger(__file__)

CURRENT_PATH = os.path.dirname(__file__)
if CURRENT_PATH:
    CURRENT_PATH = CURRENT_PATH + "/"

"""
希望得到的数据:
:公司同行业的全国分布?
:公司招聘职位类型的数量,历史变化
-----职位类型按招聘描述进行分类,不以category作为分类,来源不统一,分类较简单
:一类职位的分析,包括薪水,学历,工作经验要求,类似公司中的比较
:职业类型的发布持续时间,职位类型的热度
:职位的关键字(文字云,jieba包括:welfare字段)

"""

# -------------------- company set info ----------------------------
SEARCH_SIMILAR_COMPANY_SET = """
                             SELECT b.company_name FROM recruitment_monitor_company a,recruitment_monitor_company b WHERE
                             a.company_name=%s AND a.scale = b.scale AND a.location = b.location AND b.industry &&a.industry
                             GROUP BY b.company_name;
                             """

SEARCH_SIMILAR_COMPANY_RECRUITMENT_INFO = """
                                          SELECT * FROM recruitment WHERE company_name IN
                                          (SELECT b.company_name FROM recruitment_monitor_company a,
                                          recruitment_monitor_company b WHERE a.company_name=%s AND
                                          a.scale = b.scale AND a.location = b.location AND
                                          b.industry &&a.industry
                                          GROUP BY b.company_name);
                                          """

SEARCH_SIMILAR_COMPANY_AVG_SALARY = """
                                    SELECT AVG(salary) FROM recruitment WHERE company_name IN
                                      (SELECT b.company_name FROM recruitment_monitor_company a,
                                      recruitment_monitor_company b WHERE a.company_name=%s AND
                                      a.scale = b.scale AND a.location = b.location AND
                                      b.industry &&a.industry
                                      GROUP BY b.company_name)
                                    """

SEARCH_SIMILAR_COMPANY_DEGREE_DIS = """
                                    SELECT degree,count(degree) FROM recruitment WHERE company_name IN
                                      (SELECT b.company_name FROM recruitment_monitor_company a,
                                      recruitment_monitor_company b WHERE a.company_name=%s AND
                                      a.scale = b.scale AND a.location = b.location AND
                                      b.industry &&a.industry
                                      GROUP BY b.company_name) GROUP BY degree
                                    """

SEARCH_SIMILAR_COMPANY_CATEGORY_DIS = """
                                    SELECT category,count(category) FROM recruitment WHERE company_name IN
                                      (SELECT b.company_name FROM recruitment_monitor_company a,
                                      recruitment_monitor_company b WHERE a.company_name=%s AND
                                      a.scale = b.scale AND a.location = b.location AND
                                      b.industry &&a.industry
                                      GROUP BY b.company_name) GROUP BY category
                                    """
SEARCH_SIMILAR_COMPANY_CATEGORY_TYPE_DIS = """
                                    SELECT category_type,count(category_type) FROM recruitment WHERE company_name IN
                                      (SELECT b.company_name FROM recruitment_monitor_company a,
                                      recruitment_monitor_company b WHERE a.company_name=%s AND
                                      a.scale = b.scale AND a.location = b.location AND
                                      b.industry &&a.industry
                                      GROUP BY b.company_name) GROUP BY category_type
                                    """

SEARCH_SIMILAR_COMPANY_AVG_WORKING_YEARS = """
                                          SELECT AVG(working_years) FROM recruitment WHERE company_name IN
                                          (SELECT b.company_name FROM recruitment_monitor_company a,
                                          recruitment_monitor_company b WHERE a.company_name=%s AND
                                          a.scale = b.scale AND a.location = b.location AND
                                          b.industry &&a.industry
                                          GROUP BY b.company_name)
                                          """

# ---------------- company set info end --------------------------------


# ----------------- single company info ---------------------------------
SEARCH_COMPANY_AVG_SALARY = """
                            SELECT AVG(salary) FROM recruitment WHERE company_name=%s;
                            """

SEARCH_COMPANY_DEGREE_DIS = """
                            SELECT degree,count(degree) FROM recruitment WHERE company_name=%s GROUP BY degree;
                            """

SEARCH_COMPANY_AVG_WORKING_YEARS = """
                                    SELECT AVG(working_years) FROM recruitment WHERE company_name=%s;
                                   """

SEARCH_COMPANY_CATEGORYTYPE_BY_TIME_STEP = """
                                      SELECT to_char(release_time,'YYYY-MM') as time_step,category_type,count(category_type)
                                      FROM recruitment WHERE company_name=%s GROUP BY to_char(release_time,'YYYY-MM'),category_type order by time_step desc;
                                       """

SEARCH_COMPANY_CATEGORYTYPE_DIS = """
                                  SELECT category_type,count(category_type)
                                  FROM recruitment WHERE company_name=%s GROUP BY category_type;
                                  """

SEARCH_COMPANY_CATEGORY_DIS = """
                                SELECT category_type,category,count(category)
                                FROM recruitment WHERE company_name=%s
                                GROUP BY category_type,category order by category_type;
                              """

SEARCH_COMPANY_INFO_BY_TIME_STEP_PRE_MONTH = """
                                                SELECT to_char(release_time,'YYYY-MM') as time_step,* FROM recruitment
                                                WHERE company_name=%s AND to_char(release_time,'YYYY-MM')<%s
                                                ORDER BY time_step;
                                               """
SEARCH_COMPANY_INFO_BY_TIME_STEP_THIS_MONTH = """
                                                SELECT to_char(release_time,'YYYY-MM') as time_step,* FROM recruitment
                                                WHERE company_name=%s AND to_char(release_time,'YYYY-MM')=%s
                                                ORDER BY time_step;
                                               """

# ----------------- single company info end -----------------------------


# ----------------- total info ------------------------------------------
SEARCH_CATEGORYTYPE_LOCATION_DIS = """
                                   select category_type,city,count(city) as num from recruitment group by city,category_type order by category_type,num desc;
                                   """

SEARCH_CATEGORYTYPE_LOCATION_DIS_BY_TIME_STEP = """
                                                select to_char(release_time,'YYYY-MM') as time_step,category_type,city,count(city) from recruitment
                                                group by city,category_type,time_step
                                                order by time_step desc,category_type;
                                                """

SEARCH_CATEGORYTYPE_SALARY_AVG = """
                                 select category_type,category,avg(salary)  as avg_salary from recruitment
                                 group by category,category_type order by category_type,avg_salary desc
                                 """
SEARCH_CATEGORYTYPE_WORKING_YEARS_AVG = """
                                 select category_type,category,avg(working_years)  as avg_working_years from recruitment
                                 group by category,category_type order by category_type,avg_working_years desc
                                 """
SEARCH_CATEGORYTYPE_DIS_BY_TIME_STEP = """
                                        select to_char(release_time,'YYYY-MM') as time_step,category_type,count(category_type)
                                        from recruitment
                                        group by category_type,to_char(release_time,'YYYY-MM')
                                        order by time_step desc,category_type;
                                        """

# ----------------- total info end --------------------------------------


pg = PgUtil()
# conn = pg.put_conn()

if __name__ == '__main__':
    result = pg.select_all_sql(SEARCH_COMPANY_CATEGORYTYPE_BY_TIME_STEP, (u'杭州誉存科技有限公司',))
    print result